/*************************************************
AddDistrict
**************************************************/

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddDistrict') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddDistrict
GO

create procedure AddDistrict
   @districtName nvarchar(50)
   ,@parentId  int
   ,@ordervalue int
as
   insert into [district]
   (
      [parentid]
      ,[districtname]
      ,[ordervalue]
   )values(
      @parentId
      ,@districtName
      ,@ordervalue
   )
   
GO


/*************************************************
DelDistrict
**************************************************/

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelDistrict') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelDistrict
GO

create procedure DelDistrict
  @condstr nvarchar(128)
as
   Declare @strSQL nvarchar(256)
   set @strSQL = 'delete from [district] where ' + @condstr
   exec(@strSQL)
GO


/*************************************************
UpdateDistrict
**************************************************/

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateDistrict') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateDistrict
GO

create procedure UpdateDistrict
  @id int
  ,@name nvarchar(50)
  ,@parentId int
  ,@ordervalue int
as
   update [district] set
     [parentid] = @parentId
     ,[districtname] = @name
     ,[ordervalue] = @ordervalue
  where [id]=@id
GO


/*************************************************
GetDistrict
**************************************************/

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDistrict') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDistrict
GO

create procedure GetDistrict
  @id int
as

  select 
      'Id' = [id]
      ,'Name' = [districtname]
      ,'ParentId' = [parentid]
      ,'OrderValue' = [ordervalue]
   from [district] 
   where [id]=@id 
   
GO

/*************************************************
GetDistrictList
**************************************************/

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDistrictList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDistrictList
GO

create procedure GetDistrictList
  @condstr nvarchar(512)
as
  declare @strSQL nvarchar(600)
  set @strSQL = ' select 
        ''Id'' = [id]
        ,''Name'' = [districtname]
        ,''ParentId'' = [parentid]
        ,''OrderValue'' = [ordervalue]
     from [district] '
  if @condstr <> ''
	  begin
		set @strSQL = @strSQL + ' where ' + @condstr
	  end

  set @strSQL = @strSQL + ' order by [ordervalue] desc '

  exec(@strSQL)
   
GO




if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddDistrict2') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddDistrict2
GO


create procedure [dbo].[AddDistrict2]
   @districtName nvarchar(50)
   ,@parentName  nvarchar(50)
   ,@ordervalue int
as
   declare @sqlSTR nvarchar(100)
   declare @parentId int

   set @sqlSTR =' SELECT   @parentId = [id] FROM [district]
        where [districtname] = ''' + @parentName + ''''
        
   exec  sp_executesql @sqlSTR, N'@parentId int output' ,@parentId output

   insert into [district]
   (
      [parentid]
      ,[districtname]
      ,[ordervalue]
   )values(
       @parentId
      ,@districtName
      ,@ordervalue
   )
GO